SHOW¶
Contents
DESC, DESCRIBE¶
It shows the column information of a table, and it's like a SHOW COLUMNS statement. For more details, see SHOW COLUMNS.
DESC tbl_name;
DESCRIBE tbl_name;
EXPLAIN¶
It shows the column information of a table, and it's like a SHOW COLUMNS statement. For more details, see SHOW COLUMNS.
EXPLAIN tbl_name;
SHOW TABLES¶
It shows the list of all table names within a database. The name of the result column will be tables_in_<database name> and it will have one column. If you use the LIKE clause, you can search the table names matching this and if you use the WHERE clause, you can search table names with more general terms. SHOW FULL TABLES displays the second column, table_type together. The table must have the value, BASE TABLE and the view has the value, VIEW.
SHOW [FULL] TABLES [LIKE 'pattern' | WHERE expr];
The following shows the examples of this syntax.
SHOW TABLES;
Tables_in_demodb
======================
'athlete'
'code'
'event'
'game'
'history'
'nation'
'olympic'
'participant'
'record'
'stadium'
SHOW FULL TABLES;
Tables_in_demodb Table_type
============================================
'athlete' 'BASE TABLE'
'code' 'BASE TABLE'
'event' 'BASE TABLE'
'game' 'BASE TABLE'
'history' 'BASE TABLE'
'nation' 'BASE TABLE'
'olympic' 'BASE TABLE'
'participant' 'BASE TABLE'
'record' 'BASE TABLE'
'stadium' 'BASE TABLE'
SHOW FULL TABLES LIKE '%c%';
Tables_in_demodb Table_type
============================================
'code' 'BASE TABLE'
'olympic' 'BASE TABLE'
'participant' 'BASE TABLE'
'record' 'BASE TABLE'
SHOW FULL TABLES WHERE table_type = 'BASE TABLE' and TABLES_IN_demodb LIKE '%co%';
Tables_in_demodb Table_type
============================================
'code' 'BASE TABLE'
'record' 'BASE TABLE'
SHOW COLUMNS¶
It shows the column information of a table. You can use the LIKE clause to search the column names matching it. If you use the WHERE clause, you can search column names with more general terms like, "General Considerations for All SHOW Statements.".
SHOW [FULL] COLUMNS {FROM | IN} tbl_name [LIKE 'pattern' | WHERE expr];
If a FULL keyword is used, it shows the additional information, collation and comment.
SHOW FIELDS is the same statement as SHOW COLUMNS.
The DESCRIBE (abbreviated DESC) statement and the EXPLAIN statement provide the same information with SHOW COLUMNS, but they don't support LIKE clause or WHERE clause.
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Field | VARCHAR | Column name |
| Type | VARCHAR | Column data type |
| Null | VARCHAR | If you can store NULL, the value is YES; if not, it is NO |
| Key | VARCHAR |
|
| Default | VARCHAR | Default value defined in the column |
| Extra | VARCHAR | Additional information available on the given column. For the column with AUTO_INCREMENT constraint, it shows the 'auto_increment'. |
The following shows the examples of this syntax.
SHOW COLUMNS FROM athlete;
Field Type Null Key Default Extra
================================================================================================================
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'
'name' 'VARCHAR(40)' 'NO' '' NULL ''
'gender' 'CHAR(1)' 'YES' '' NULL ''
'nation_code' 'CHAR(3)' 'YES' '' NULL ''
'event' 'VARCHAR(30)' 'YES' '' NULL ''
SHOW COLUMNS FROM athlete WHERE field LIKE '%c%';
Field Type Null Key Default Extra
================================================================================================================
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'
'nation_code' 'CHAR(3)' 'YES' '' NULL ''
SHOW COLUMNS FROM athlete WHERE "type" = 'INTEGER' and "key"='PRI' AND extra='auto_increment';
Field Type Null Key Default Extra
================================================================================================================
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'
SHOW FULL COLUMNS FROM athlete WHERE field LIKE '%c%';
Field Type Collation Null Key Default Extra Comment
================================================================================================================================================================================
'code' 'INTEGER' NULL 'NO' 'PRI' NULL 'auto_increment' NULL
'nation_code' 'CHAR(3)' 'iso88591_bin' 'YES' '' NULL '' NULL
SHOW INDEX¶
It shows the index information.
SHOW {INDEX | INDEXES | KEYS } {FROM | IN} tbl_name;
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Table | VARCHAR | Table name |
| Non_unique | INTEGER |
|
| Key_name | VARCHAR | Index name |
| Seq_in_index | INTEGER | Serial number of the column in the index. Starts from 1. |
| Column_name | VARCHAR | Column name |
| Collation | VARCHAR | Method of sorting columns in the index. 'A' means ascending and NULL means not sorted. |
| Cardinality | INTEGER | The number of values measuring the unique values in the index. Higher cardinality increases the opportunity of using an index. This value is updated every time SHOW INDEX is executed. Note that this is an approximate value. |
| Sub_part | INTEGER | The number of bytes of the indexed characters if the columns are indexed partially. NULL if all columns are indexed. |
| Packed | Shows how keys are packed. If they are not packed, it will be NULL. Currently no support. | |
| Null | VARCHAR | YES if a column can include NULL, NO if not. |
| Index_type | VARCHAR | Index to be used (currently, only the BTREE is supported.) |
| Func | VARCHAR | A function which is used in a function-based index |
| Comment | VARCHAR | Comment to describe the index |
The following shows the examples of this syntax.
SHOW INDEX IN athlete;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null
Index_type Func Comment
=================================================================================================================================================================================
==========================================================================
'athlete' 0 'pk_athlete_code' 1 'code' 'A' 6677 NULL NULL 'NO'
'BTREE' NULL NULL
CREATE TABLE tbl1 (i1 INTEGER , i2 INTEGER NOT NULL, i3 INTEGER UNIQUE, s1 VARCHAR(10), s2 VARCHAR(10), s3 VARCHAR(10) UNIQUE);
CREATE INDEX i_tbl1_i1 ON tbl1 (i1 DESC);
CREATE INDEX i_tbl1_s1 ON tbl1 (s1 (7));
CREATE INDEX i_tbl1_i1_s1 ON tbl1 (i1, s1);
CREATE UNIQUE INDEX i_tbl1_i2_s2 ON tbl1 (i2, s2);
SHOW INDEXES FROM tbl1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null
Index_type Func Comment
=================================================================================================================================================================================
==========================================================================
'tbl1' 1 'i_tbl1_i1' 1 'i1' 'D' 0 NULL NULL 'YES'
'BTREE' NULL NULL
'tbl1' 1 'i_tbl1_i1_s1' 1 'i1' 'A' 0 NULL NULL 'YES'
'BTREE' NULL NULL
'tbl1' 1 'i_tbl1_i1_s1' 2 's1' 'A' 0 NULL NULL 'YES'
'BTREE' NULL NULL
'tbl1' 0 'i_tbl1_i2_s2' 1 'i2' 'A' 0 NULL NULL 'NO'
'BTREE' NULL NULL
'tbl1' 0 'i_tbl1_i2_s2' 2 's2' 'A' 0 NULL NULL 'YES'
'BTREE' NULL NULL
'tbl1' 1 'i_tbl1_s1' 1 's1' 'A' 0 7 NULL 'YES'
'BTREE' NULL NULL
'tbl1' 0 'u_tbl1_i3' 1 'i3' 'A' 0 NULL NULL 'YES'
'BTREE' NULL NULL
'tbl1' 0 'u_tbl1_s3' 1 's3' 'A' 0 NULL NULL 'YES'
'BTREE' NULL NULL
SHOW COLLATION¶
It lists collations supported by the database. If LIKE clause is present, it indicates which collation names to match.
SHOW COLLATION [ LIKE 'pattern' ];
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Collation | VARCHAR | Collation name |
| Charset | CHAR(1) | Charset name |
| Id | INTEGER | Collation ID |
| Built_in | CHAR(1) | Built-in collation or not. Built-in collations are impossible to add or remove because they are hard-coded. |
| Expansions | CHAR(1) | Collation with expansion or not. For details, see Expansion. |
| Strength | CHAR(1) | The number of levels to be considered in comparison, and the character order can be different by this number. For details, see Collation Properties. |
The following shows the examples of this syntax.
SHOW COLLATION;
Collation Charset Id Built_in Expansions Strength
===========================================================================================================================
'euckr_bin' 'euckr' 8 'Yes' 'No' 'Not applicable'
'iso88591_bin' 'iso88591' 0 'Yes' 'No' 'Not applicable'
'iso88591_en_ci' 'iso88591' 3 'Yes' 'No' 'Not applicable'
'iso88591_en_cs' 'iso88591' 2 'Yes' 'No' 'Not applicable'
'utf8_bin' 'utf8' 1 'Yes' 'No' 'Not applicable'
'utf8_de_exp' 'utf8' 76 'No' 'Yes' 'Tertiary'
'utf8_de_exp_ai_ci' 'utf8' 72 'No' 'Yes' 'Primary'
'utf8_en_ci' 'utf8' 5 'Yes' 'No' 'Not applicable'
'utf8_en_cs' 'utf8' 4 'Yes' 'No' 'Not applicable'
'utf8_es_cs' 'utf8' 85 'No' 'No' 'Quaternary'
'utf8_fr_exp_ab' 'utf8' 94 'No' 'Yes' 'Tertiary'
'utf8_gen' 'utf8' 32 'No' 'No' 'Quaternary'
'utf8_gen_ai_ci' 'utf8' 37 'No' 'No' 'Primary'
'utf8_gen_ci' 'utf8' 44 'No' 'No' 'Secondary'
'utf8_ja_exp' 'utf8' 124 'No' 'Yes' 'Tertiary'
'utf8_ja_exp_cbm' 'utf8' 125 'No' 'Yes' 'Tertiary'
'utf8_km_exp' 'utf8' 132 'No' 'Yes' 'Quaternary'
'utf8_ko_cs' 'utf8' 7 'Yes' 'No' 'Not applicable'
'utf8_ko_cs_uca' 'utf8' 133 'No' 'No' 'Quaternary'
'utf8_tr_cs' 'utf8' 6 'Yes' 'No' 'Not applicable'
'utf8_tr_cs_uca' 'utf8' 205 'No' 'No' 'Quaternary'
'utf8_vi_cs' 'utf8' 221 'No' 'No' 'Quaternary'
SHOW COLLATION LIKE '%_ko_%';
Collation Charset Id Built_in Expansions Strength
===========================================================================================================================
'utf8_ko_cs' 'utf8' 7 'Yes' 'No' 'Not applicable'
'utf8_ko_cs_uca' 'utf8' 133 'No' 'No' 'Quaternary'
SHOW TIMEZONES¶
It shows the timezone information which the current CUBRID supports.
SHOW [FULL] TIMEZONES [ LIKE 'pattern' ];
If FULL is not specified, one column which has timezone's region names is displayed. The name of this column is timezone_region.
If FULL is specified, four columns which have timezone information are displayed.
If LIKE clause is present, it indicates which timezone_region names to match.
| Column name | Type | Description |
|---|---|---|
| timezone_region | VARCHAR(32) | Timezone region name |
| region_offset | VARCHAR(32) | Offset of timezone (daylight saving time is not considered) |
| dst_offset | VARCHAR(32) | Offset of daylight saving time (applied to timezone region) which is currently considered |
| dst_abbreviation | VARCHAR(32) | An abbreviation of the daylight saving time which is currently applied for the region |
The information listed for the second, third and fourth columns is for the current date and time.
If a timezone region doesn't have daylight saving time rules at all then the dst_offset and dst_abbreviation columns will contain NULL values.
If at the current date, there aren't daylight saving time rules that apply, then dst_offset will be set to 0 and dst_abbreviation will be the empty string.
The LIKE condition without the WHERE condition is applied on the first column. The WHERE condition may be used to filter the output.
SHOW TIMEZONES;
timezone_region
======================
'Africa/Abidjan'
'Africa/Accra'
'Africa/Addis_Ababa'
'Africa/Algiers'
'Africa/Asmara'
'Africa/Asmera'
...
'US/Michigan'
'US/Mountain'
'US/Pacific'
'US/Samoa'
'UTC'
'Universal'
'W-SU'
'WET'
'Zulu'
SHOW FULL TIMEZONES;
timezone_region region_offset dst_offset dst_abbreviation
===================================================================================
'Africa/Abidjan' '+00:00' '+00:00' 'GMT'
'Africa/Accra' '+00:00' NULL NULL
'Africa/Addis_Ababa' '+03:00' '+00:00' 'EAT'
'Africa/Algiers' '+01:00' '+00:00' 'CET'
'Africa/Asmara' '+03:00' '+00:00' 'EAT'
'Africa/Asmera' '+03:00' '+00:00' 'EAT'
...
'US/Michigan' '-05:00' '+00:00' 'EST'
'US/Mountain' '-07:00' '+00:00' 'MST'
'US/Pacific' '-08:00' '+00:00' 'PST'
'US/Samoa' '-11:00' '+00:00' 'SST'
'UTC' '+00:00' '+00:00' 'UTC'
'Universal' '+00:00' '+00:00' 'UTC'
'W-SU' '+04:00' '+00:00' 'MSK'
'WET' '+00:00' '+00:00' 'WET'
'Zulu' '+00:00' '+00:00' 'UTC'
SHOW FULL TIMEZONES LIKE '%Paris%';
timezone_region region_offset dst_offset dst_abbreviation
========================================================================================
'Europe/Paris' '+01:00' '+00:00' 'CET'
SHOW GRANTS¶
It shows the permissions associated with the database user accounts.
SHOW GRANTS FOR 'user';
The following shows the examples of this syntax.
CREATE TABLE testgrant (id INT);
CREATE USER user1;
GRANT INSERT,SELECT ON testgrant TO user1;
SHOW GRANTS FOR user1;
Grants for USER1
======================
'GRANT INSERT, SELECT ON testgrant TO USER1'
SHOW CREATE TABLE¶
When a table name is specified, It shows the CREATE TABLE statement of the table.
SHOW CREATE TABLE table_name;
SHOW CREATE TABLE nation;
TABLE CREATE TABLE
============================================
'nation' 'CREATE TABLE [nation] ([code] CHARACTER(3) NOT NULL,
[name] CHARACTER VARYING(40) NOT NULL, [continent] CHARACTER VARYING(10),
[capital] CHARACTER VARYING(30), CONSTRAINT [pk_nation_code] PRIMARY KEY ([code]))
COLLATE iso88591_bin'
SHOW CREATE TABLE statement does not display as the user's written syntax. For example, the comment that user wrote is not displayed, and table names and column names are always displayed as lower case letters.
SHOW CREATE VIEW¶
It shows the corresponding CREATE VIEW statement if view name is specified.
SHOW CREATE VIEW view_name;
The following shows the examples of this syntax.
SHOW CREATE VIEW db_class;
View Create View
========================================
'db_class' 'SELECT c.class_name, CAST(c.owner.name AS VARCHAR(255)), CASE c.class_type WHEN 0 THEN 'CLASS' WHEN 1 THEN 'VCLASS' ELSE
'UNKNOW' END, CASE WHEN MOD(c.is_system_class, 2) = 1 THEN 'YES' ELSE 'NO' END, CASE WHEN c.sub_classes IS NULL THEN 'NO'
ELSE NVL((SELECT 'YES' FROM _db_partition p WHERE p.class_of = c and p.pname IS NULL), 'NO') END, CASE WHEN
MOD(c.is_system_class / 8, 2) = 1 THEN 'YES' ELSE 'NO' END FROM _db_class c WHERE CURRENT_USER = 'DBA' OR {c.owner.name}
SUBSETEQ ( SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{t.g.name}), SET{}) FROM db_user u, TABLE(groups) AS t(g) WHERE
u.name = CURRENT_USER) OR {c} SUBSETEQ ( SELECT SUM(SET{au.class_of}) FROM _db_auth au WHERE {au.grantee.name} SUBSETEQ
( SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{t.g.name}), SET{}) FROM db_user u, TABLE(groups) AS t(g) WHERE u.name =
CURRENT_USER) AND au.auth_type = 'SELECT')'
SHOW ACCESS STATUS¶
SHOW ACCESS STATUS statement displays login information regarding database accounts. Only database's DBA account can use this statement.
SHOW ACCESS STATUS [LIKE 'pattern' | WHERE expr] ;
This statement displays the following columns.
| Column name | Type | Description |
|---|---|---|
| user_name | VARCHAR(32) | DB user's account |
| last_access_time | DATETIME | Last time that the database user accessed |
| last_access_host | VARCHAR(32) | Lastly accessed host |
| program_name | VARCHAR(32) | The name of client program(broker_cub_cas_1, csql ..) |
The following shows the result of running this statement.
SHOW ACCESS STATUS;
user_name last_access_time last_access_host program_name
=============================================================================
'DBA' 08:19:31.000 PM 02/10/2014 127.0.0.1 'csql'
'PUBLIC' NULL NULL NULL
Note
The above login information which SHOW ACCESS STATUS shows is initialized when the database is restarted, and this query is not replication in HA environment; therefore, each node shows the different result.
SHOW EXEC STATISTICS¶
It shows statistics information of executing query.
- To start collecting @collect_exec_stats statistics information, configure the value of session variable @collect_exec_stats to 1; to stop, configure it to 0.
- It outputs the result of collecting statistics information.
- The SHOW EXEC STATISTICS statement outputs four part of data page statistics information; data_page_fetches, data_page_dirties, data_page_ioreads, and data_page_iowrites. The result columns consist of variable column (name of statistics name) and value column (value of statistics value). Once the SHOW EXEC STATISTICS statement is executed, the statistics information which has been accumulated is initialized.
- The SHOW EXEC STATISTICS ALL statement outputs all items of statistics information.
For details, see statdump.
SHOW EXEC STATISTICS [ALL];
The following shows the examples of this syntax.
-- set session variable @collect_exec_stats as 1 to start collecting the statistical information.
SET @collect_exec_stats = 1;
SELECT * FROM db_class;
-- print the statistical information of the data pages.
SHOW EXEC STATISTICS;
variable value
===============================
'data_page_fetches' 332
'data_page_dirties' 85
'data_page_ioreads' 18
'data_page_iowrites' 28
SELECT * FROM db_index;
-- print all of the statistical information.
SHOW EXEC STATISTICS ALL;
variable value
============================================
'file_creates' 0
'file_removes' 0
'file_ioreads' 6
'file_iowrites' 0
'file_iosynches' 0
'data_page_fetches' 548
'data_page_dirties' 34
'data_page_ioreads' 6
'data_page_iowrites' 0
'log_page_ioreads' 0
'log_page_iowrites' 0
'log_append_records' 0
'log_archives' 0
'log_start_checkpoints' 0
'log_end_checkpoints' 0
'log_wals' 0
'page_locks_acquired' 13
'object_locks_acquired' 9
'page_locks_converted' 0
'object_locks_converted' 0
'page_locks_re-requested' 0
'object_locks_re-requested' 8
'page_locks_waits' 0
'object_locks_waits' 0
'tran_commits' 3
'tran_rollbacks' 0
'tran_savepoints' 0
'tran_start_topops' 6
'tran_end_topops' 6
'tran_interrupts' 0
'btree_inserts' 0
'btree_deletes' 0
'btree_updates' 0
'btree_covered' 0
'btree_noncovered' 2
'btree_resumes' 0
'btree_multirange_optimization' 0
'query_selects' 4
'query_inserts' 0
'query_deletes' 0
'query_updates' 0
'query_sscans' 2
'query_iscans' 4
'query_lscans' 0
'query_setscans' 2
'query_methscans' 0
'query_nljoins' 2
'query_mjoins' 0
'query_objfetches' 0
'query_holdable_cursors' 0
'sort_io_pages' 0
'sort_data_pages' 0
'network_requests' 88
'adaptive_flush_pages' 0
'adaptive_flush_log_pages' 0
'adaptive_flush_max_pages' 0
'prior_lsa_list_size' 0
'prior_lsa_list_maxed' 0
'prior_lsa_list_removed' 0
'heap_stats_bestspace_entries' 0
'heap_stats_bestspace_maxed' 0
Diagnostics¶
SHOW VOLUME HEADER¶
It shows the volume header of the specified volume in one row.
SHOW VOLUME HEADER OF volume_id;
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Volume_id | INT | Volume identifier |
| Magic_symbol | VARCHAR(100) | Magic value for for a volume file |
| Io_page_size | INT | Size of DB volume |
| Purpose | VARCHAR(32) | Volume purposes, 'Permanent data purpose' or 'Temporary data purpose' |
| Type | VARCHAR(32) | Volume type, 'Permanent Volume' or 'Temporary Volume' |
| Sector_size_in_pages | INT | Size of sector in pages |
| Num_total_sectors | INT | Total number of sectors |
| Num_free_sectors | INT | Number of free sectors |
| Num_max_sectors | INT | Maximum number of sectors |
| Hint_alloc_sector | INT | Hint for next sector to be allocated |
| Sector_alloc_table_size_in_pages | INT | Size of sector allocation table in page |
| Sector_alloc_table_first_page | INT | First page of sector allocation table |
| Page_alloc_table_size_in_pages | INT | Size of page allocation table in page |
| Page_alloc_table_first_page | INT | First page of page allocation table |
| Last_system_page | INT | Last system page |
| Creation_time | DATETIME | Database creation time |
| Db_charset | INT | Charset number of database |
| Checkpoint_lsa | VARCHAR(64) | Lowest log sequence address to start the recovery process of this volume |
| Boot_hfid | VARCHAR(64) | System Heap file for booting purposes and multi volumes |
| Full_name | VARCHAR(255) | The full path of volume |
| Next_volume_id | INT | Next volume identifier |
| Next_vol_full_name | VARCHAR(255) | The full path of next volume |
| Remarks | VARCHAR(64) | Volume remarks |
The following shows the examples of this syntax.
-- csql> ;line on
SHOW VOLUME HEADER OF 0;
<00001> Volume_id : 0
Magic_symbol : 'MAGIC SYMBOL = CUBRID/Volume at disk location = 32'
Io_page_size : 16384
Purpose : 'Permanent data purpose'
Type : 'Permanent Volume'
Sector_size_in_pages : 64
Num_total_sectors : 512
Num_free_sectors : 459
Num_max_sectors : 512
Hint_alloc_sector : 0
Sector_alloc_table_size_in_pages: 1
Sector_alloc_table_first_page : 1
Last_system_page : 1
Creation_time : 09:46:41.000 PM 05/23/2017
Db_charset : 3
Checkpoint_lsa : '(0|12832)'
Boot_hfid : '(0|41|50)'
Full_name : '/home1/brightest/CUBRID/databases/demodb/demodb'
Next_volume_id : -1
Next_vol_full_name : ''
Remarks : ''
SHOW LOG HEADER¶
It shows the header information of an active log file.
SHOW LOG HEADER [OF file_name];
If you omit OF file_name, it shows the header information of a memory; if you include OF file_name, it shows the header information of file_name.
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Volume_id | INT | Volume identifier |
| Magic_symbol | VARCHAR(32) | Magic value for log file |
| Magic_symbol_location | INT | Magic symbol location from log page |
| Creation_time | DATETIME | Database creation time |
| Release | VARCHAR(32) | CUBRID Release version |
| Compatibility_disk_version | VARCHAR(32) | Compatibility of the database against the current release of CUBRID |
| Db_page_size | INT | Size of pages in the database |
| Log_page_size | INT | Size of log pages in the database |
| Shutdown | INT | Was the log shutdown |
| Next_trans_id | INT | Next transaction identifier |
| Num_avg_trans | INT | Number of average transactions |
| Num_avg_locks | INT | Average number of object locks |
| Num_active_log_pages | INT | Number of pages in the active log portion |
| Db_charset | INT | Charset number of database |
| First_active_log_page | BIGINT | Logical pageid at physical location 1 in active log |
| Current_append | VARCHAR(64) | Current append location |
| Checkpoint | VARCHAR(64) | Lowest log sequence address to start the recovery process |
| Next_archive_page_id | BIGINT | Next logical page to archive |
| Active_physical_page_id | INT | Physical location of logical page to archive |
| Next_archive_num | INT | Next log archive number |
| Last_archive_num_for_syscrashes | INT | Last log archive needed for system crashes |
| Last_deleted_archive_num | INT | Last deleted archive number |
| Backup_lsa_level0 | VARCHAR(64) | LSA of backup level 0 |
| Backup_lsa_level1 | VARCHAR(64) | LSA of backup level 1 |
| Backup_lsa_level2 | VARCHAR(64) | LSA of backup level 2 |
| Log_prefix | VARCHAR(256) | Log prefix name |
| Has_logging_been_skipped | INT | Whether or not logging skipped |
| Perm_status | VARCHAR(64) | Reserved for future expansion |
| Backup_info_level0 | VARCHAR(128) | detail information of backup level 0. currently only backup start-time is used |
| Backup_info_level1 | VARCHAR(128) | detail information of backup level 1. currently only backup start-time is used |
| Backup_info_level2 | VARCHAR(128) | detail information of backup level 2. currently only backup start-time is used |
| Ha_server_state | VARCHAR(32) | current ha state, one of flowing value: na, idle, active, to-be-active, standby, to-be-standby, maintenance, dead |
| Ha_file | VARCHAR(32) | ha replication status, one of following value: clear, archived, sync |
| Eof_lsa | VARCHAR(64) | EOF LSA |
| Smallest_lsa_at_last_checkpoint | VARCHAR(64) | The smallest LSA of the last checkpoint, can be NULL LSA |
| Next_mvcc_id | BIGINT | The next MVCCID will be used for the next transaction |
| Mvcc_op_log_lsa | VARCHAR(32) | The LSA used to link log entries for MVCC operation |
| Last_block_oldest_mvcc_id | BIGINT | Used to find the oldest MVCCID in a block of log data, can be NULL |
| Last_block_newest_mvcc_id | BIGINT | Used to find the newest MVCCID in a block of log data, can be NULL |
The following shows the examples of this syntax.
-- csql> ;line on
SHOW LOG HEADER;
<00001> Volume_id : -2
Magic_symbol : 'CUBRID/LogActive'
Magic_symbol_location : 16
Creation_time : 09:46:41.000 PM 05/23/2017
Release : '10.0.0'
Compatibility_disk_version : '10'
Db_page_size : 16384
Log_page_size : 16384
Shutdown : 0
Next_trans_id : 17
Num_avg_trans : 3
Num_avg_locks : 30
Num_active_log_pages : 1279
Db_charset : 3
First_active_log_page : 0
Current_append : '(102|5776)'
Checkpoint : '(101|7936)'
Next_archive_page_id : 0
Active_physical_page_id : 1
Next_archive_num : 0
Last_archive_num_for_syscrashes: -1
Last_deleted_archive_num : -1
Backup_lsa_level0 : '(-1|-1)'
Backup_lsa_level1 : '(-1|-1)'
Backup_lsa_level2 : '(-1|-1)'
Log_prefix : 'mvccdb'
Has_logging_been_skipped : 0
Perm_status : 'LOG_PSTAT_CLEAR'
Backup_info_level0 : 'time: N/A'
Backup_info_level1 : 'time: N/A'
Backup_info_level2 : 'time: N/A'
Ha_server_state : 'idle'
Ha_file : 'UNKNOWN'
Eof_lsa : '(102|5776)'
Smallest_lsa_at_last_checkpoint: '(101|7936)'
Next_mvcc_id : 6
Mvcc_op_log_lsa : '(102|5488)'
Last_block_oldest_mvcc_id : 4
Last_block_newest_mvcc_id : 5
SHOW LOG HEADER OF 'demodb_lgat';
<00001> Volume_id : -2
Magic_symbol : 'CUBRID/LogActive'
Magic_symbol_location : 16
Creation_time : 09:46:41.000 PM 05/23/2017
Release : '10.0.0'
Compatibility_disk_version : '10'
Db_page_size : 16384
Log_page_size : 16384
Shutdown : 0
Next_trans_id : 15
Num_avg_trans : 3
Num_avg_locks : 30
Num_active_log_pages : 1279
Db_charset : 3
First_active_log_page : 0
Current_append : '(101|8016)'
Checkpoint : '(101|7936)'
Next_archive_page_id : 0
Active_physical_page_id : 1
Next_archive_num : 0
Last_archive_num_for_syscrashes: -1
Last_deleted_archive_num : -1
Backup_lsa_level0 : '(-1|-1)'
Backup_lsa_level1 : '(-1|-1)'
Backup_lsa_level2 : '(-1|-1)'
Log_prefix : 'mvccdb'
Has_logging_been_skipped : 0
Perm_status : 'LOG_PSTAT_CLEAR'
Backup_info_level0 : 'time: N/A'
Backup_info_level1 : 'time: N/A'
Backup_info_level2 : 'time: N/A'
Ha_server_state : 'idle'
Ha_file : 'UNKNOWN'
Eof_lsa : '(101|8016)'
Smallest_lsa_at_last_checkpoint: '(101|7936)'
Next_mvcc_id : 4
Mvcc_op_log_lsa : '(-1|-1)'
Last_block_oldest_mvcc_id : NULL
Last_block_newest_mvcc_id : NULL
SHOW ARCHIVE LOG HEADER¶
It shows the header information of an archive log file.
SHOW ARCHIVE LOG HEADER OF file_name;
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Volume_id | INT | Identifier of log volume |
| Magic_symbol | VARCHAR(32) | Magic value for file/magic Unix utility |
| Magic_symbol_location | INT | Magic symbol location from log page |
| Creation_time | DATETIME | Database creation time |
| Next_trans_id | BIGINT | Next transaction identifier |
| Num_pages | INT | Number of pages in the archive log |
| First_page_id | BIGINT | Logical page id at physical location 1 in archive log |
| Archive_num | INT | The archive log number |
The following shows the examples of this syntax.
-- csql> ;line on
SHOW ARCHIVE LOG HEADER OF 'demodb_lgar001';
<00001> Volume_id : -20
Magic_symbol : 'CUBRID/LogArchive'
Magic_symbol_location: 16
Creation_time : 04:42:28.000 PM 12/11/2013
Next_trans_id : 22695
Num_pages : 1278
First_page_id : 1278
Archive_num : 1
SHOW HEAP HEADER¶
It shows shows the header page of the table.
SHOW [ALL] HEAP HEADER OF table_name;
- ALL: If "ALL" is given in syntax in the partition table, the basic table and its partitioned tables are shown.
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Class_name | VARCHAR(256) | Table name |
| Class_oid | VARCHAR(64) | Format: (volid|pageid|slotid) |
| Volume_id | INT | Volume identifier where the file reside |
| File_id | INT | File identifier |
| Header_page_id | INT | First page identifier (the header page) |
| Overflow_vfid | VARCHAR(64) | Overflow file identifier (if any) |
| Next_vpid | VARCHAR(64) | Next page (i.e., the 2nd page of heap file) |
| Unfill_space | INT | Stop inserting when page has run below this. leave it for updates |
| Estimates_num_pages | BIGINT | Estimation of number of heap pages. |
| Estimates_num_recs | BIGINT | Estimation of number of objects in heap |
| Estimates_avg_rec_len | INT | Estimation total length of records |
| Estimates_num_high_best | INT | Number of pages in the best array that we believe have at least HEAP_DROP_FREE_SPACE. When this number goes to zero and there are at least other HEAP_NUM_BEST_SPACESTATS best pages, we look for them |
| Estimates_num_others_high_best | INT | Total of other believed known best pages, which are not included in the best array and we believe they have at least HEAP_DROP_FREE_SPACE |
| Estimates_head | INT | Head of best circular array |
| Estimates_best_list | VARCHAR(512) | Format: '((best[0].vpid.volid|best[0].vpid.pageid), best[0].freespace), ... , ((best[9].vpid.volid|best[9].vpid.pageid), best[9].freespace)' |
| Estimates_num_second_best | INT | Number of second best hints. The hints are in "second_best" array. They are used when finding new best pages. |
| Estimates_head_second_best | INT | Index of head of second best hints. A new second best hint will be stored on this index. |
| Estimates_num_substitutions | INT | Number of page substitutions. This will be used to insert a new second best page into second best hints. |
| Estimates_second_best_list | VARCHAR(512) | Format: '(second_best[0].vpid.volid|second_best[0].vpid.pageid), ... , (second_best[9].vpid.volid|second_best[9].vpid.pageid)' |
| Estimates_last_vpid | VARCHAR(64) | Format: '(volid|pageid)' |
| Estimates_full_search_vpid | VARCHAR(64) | Format: '(volid|pageid)' |
The following shows the examples of this syntax.
-- csql> ;line on
SHOW HEAP HEADER OF athlete;
<00001> Class_name : 'athlete'
Class_oid : '(0|463|8)'
Volume_id : 0
File_id : 147
Header_page_id : 590
Overflow_vfid : '(-1|-1)'
Next_vpid : '(0|591)'
Unfill_space : 1635
Estimates_num_pages : 27
Estimates_num_recs : 6677
Estimates_avg_rec_len : 54
Estimates_num_high_best : 1
Estimates_num_others_high_best: 0
Estimates_head : 0
Estimates_best_list : '((0|826), 14516), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1),0), ((-1|-1), 0)'
Estimates_num_second_best : 0
Estimates_head_second_best : 0
Estimates_tail_second_best : 0
Estimates_num_substitutions : 0
Estimates_second_best_list : '(-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1)'
Estimates_last_vpid : '(0|826)'
Estimates_full_search_vpid : '(0|590)'
CREATE TABLE participant2 (
host_year INT,
nation CHAR(3),
gold INT,
silver INT,
bronze INT
)
PARTITION BY RANGE (host_year) (
PARTITION before_2000 VALUES LESS THAN (2000),
PARTITION before_2008 VALUES LESS THAN (2008)
);
SHOW ALL HEAP HEADER OF participant2;
<00001> Class_name : 'participant2'
Class_oid : '(0|467|6)'
Volume_id : 0
File_id : 374
Header_page_id : 940
Overflow_vfid : '(-1|-1)'
Next_vpid : '(-1|-1)'
Unfill_space : 1635
Estimates_num_pages : 1
Estimates_num_recs : 0
Estimates_avg_rec_len : 0
Estimates_num_high_best : 1
Estimates_num_others_high_best: 0
Estimates_head : 1
Estimates_best_list : '((0|940), 16308), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0)'
Estimates_num_second_best : 0
Estimates_head_second_best : 0
Estimates_tail_second_best : 0
Estimates_num_substitutions : 0
Estimates_second_best_list : '(-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1)'
Estimates_last_vpid : '(0|940)'
Estimates_full_search_vpid : '(0|940)'
<00002> Class_name : 'participant2__p__before_2000'
Class_oid : '(0|467|7)'
Volume_id : 0
File_id : 376
Header_page_id : 950
Overflow_vfid : '(-1|-1)'
Next_vpid : '(-1|-1)'
Unfill_space : 1635
Estimates_num_pages : 1
Estimates_num_recs : 0
Estimates_avg_rec_len : 0
Estimates_num_high_best : 1
Estimates_num_others_high_best: 0
Estimates_head : 1
Estimates_best_list : '((0|950), 16308), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0)'
Estimates_num_second_best : 0
Estimates_head_second_best : 0
Estimates_tail_second_best : 0
Estimates_num_substitutions : 0
Estimates_second_best_list : '(-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1)'
Estimates_last_vpid : '(0|950)'
Estimates_full_search_vpid : '(0|950)'
<00003> Class_name : 'participant2__p__before_2008'
Class_oid : '(0|467|8)'
Volume_id : 0
File_id : 378
Header_page_id : 960
Overflow_vfid : '(-1|-1)'
Next_vpid : '(-1|-1)'
Unfill_space : 1635
Estimates_num_pages : 1
Estimates_num_recs : 0
Estimates_avg_rec_len : 0
Estimates_num_high_best : 1
Estimates_num_others_high_best: 0
Estimates_head : 1
Estimates_best_list : '((0|960), 16308), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0)'
Estimates_num_second_best : 0
Estimates_head_second_best : 0
Estimates_tail_second_best : 0
Estimates_num_substitutions : 0
Estimates_second_best_list : '(-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1)'
Estimates_last_vpid : '(0|960)'
Estimates_full_search_vpid : '(0|960)'
SHOW HEAP HEADER OF participant2__p__before_2008;
<00001> Class_name : 'participant2__p__before_2008'
Class_oid : '(0|467|8)'
Volume_id : 0
File_id : 378
Header_page_id : 960
Overflow_vfid : '(-1|-1)'
Next_vpid : '(-1|-1)'
Unfill_space : 1635
Estimates_num_pages : 1
Estimates_num_recs : 0
Estimates_avg_rec_len : 0
Estimates_num_high_best : 1
Estimates_num_others_high_best: 0
Estimates_head : 1
Estimates_best_list : '((0|960), 16308), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0), ((-1|-1), 0)'
Estimates_num_second_best : 0
Estimates_head_second_best : 0
Estimates_tail_second_best : 0
Estimates_num_substitutions : 0
Estimates_second_best_list : '(-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1), (-1|-1)'
Estimates_last_vpid : '(0|960)'
Estimates_full_search_vpid : '(0|960)'
SHOW HEAP CAPACITY¶
It shows the capacity of the table.
SHOW [ALL] HEAP CAPACITY OF table_name;
- ALL: If "all" is given in syntax, the basic table and its partition table(s) is shown.
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Table_name | VARCHAR(256) | Table name |
| Class_oid | VARCHAR(64) | Heap file descriptor |
| Volume_id | INT | Volume identifier where the file reside |
| File_id | INT | File identifier |
| Header_page_id | INT | First page identifier (the header page) |
| Num_recs | BIGINT | Total Number of objects |
| Num_relocated_recs | BIGINT | Number of relocated records |
| Num_overflowed_recs | BIGINT | Number of big records |
| Num_pages | BIGINT | Total number of heap pages |
| Avg_rec_len | INT | Average object length |
| Avg_free_space_per_page | INT | Average free space per page |
| Avg_free_space_per_page_without_last_page | INT | Average free space per page without taking in consideration last page |
| Avg_overhead_per_page | INT | Average overhead per page |
| Repr_id | INT | Currently cached catalog column info |
| Num_total_attrs | INT | total number of columns |
| Num_fixed_width_attrs | INT | Number of the fixed width columns |
| Num_variable_width_attrs | INT | Number of variable width columns |
| Num_shared_attrs | INT | Number of shared columns |
| Num_class_attrs | INT | Number of table columns |
| Total_size_fixed_width_attrs | INT | Total size of the fixed width columns |
The following shows the examples of this syntax.
-- csql> ;line on
SHOW HEAP CAPACITY OF athlete;
<00001> Table_name : 'athlete'
Class_oid : '(0|463|8)'
Volume_id : 0
File_id : 147
Header_page_id : 590
Num_recs : 6677
Num_relocated_recs : 0
Num_overflowed_recs : 0
Num_pages : 27
Avg_rec_len : 53
Avg_free_space_per_page : 2139
Avg_free_space_per_page_except_last_page: 1663
Avg_overhead_per_page : 993
Repr_id : 1
Num_total_attrs : 5
Num_fixed_width_attrs : 3
Num_variable_width_attrs : 2
Num_shared_attrs : 0
Num_class_attrs : 0
Total_size_fixed_width_attrs : 8
SHOW ALL HEAP CAPACITY OF participant2;
<00001> Table_name : 'participant2'
Class_oid : '(0|467|6)'
Volume_id : 0
File_id : 374
Header_page_id : 940
Num_recs : 0
Num_relocated_recs : 0
Num_overflowed_recs : 0
Num_pages : 1
Avg_rec_len : 0
Avg_free_space_per_page : 16016
Avg_free_space_per_page_except_last_page: 0
Avg_overhead_per_page : 4
Repr_id : 1
Num_total_attrs : 5
Num_fixed_width_attrs : 5
Num_variable_width_attrs : 0
Num_shared_attrs : 0
Num_class_attrs : 0
Total_size_fixed_width_attrs : 20
<00002> Table_name : 'participant2__p__before_2000'
Class_oid : '(0|467|7)'
Volume_id : 0
File_id : 376
Header_page_id : 950
Num_recs : 0
Num_relocated_recs : 0
Num_overflowed_recs : 0
Num_pages : 1
Avg_rec_len : 0
Avg_free_space_per_page : 16016
Avg_free_space_per_page_except_last_page: 0
Avg_overhead_per_page : 4
Repr_id : 1
Num_total_attrs : 5
Num_fixed_width_attrs : 5
Num_variable_width_attrs : 0
Num_shared_attrs : 0
Num_class_attrs : 0
Total_size_fixed_width_attrs : 20
<00003> Table_name : 'participant2__p__before_2008'
Class_oid : '(0|467|8)'
Volume_id : 0
File_id : 378
Header_page_id : 960
Num_recs : 0
Num_relocated_recs : 0
Num_overflowed_recs : 0
Num_pages : 1
Avg_rec_len : 0
Avg_free_space_per_page : 16016
Avg_free_space_per_page_except_last_page: 0
Avg_overhead_per_page : 4
Repr_id : 1
Num_total_attrs : 5
Num_fixed_width_attrs : 5
Num_variable_width_attrs : 0
Num_shared_attrs : 0
Num_class_attrs : 0
Total_size_fixed_width_attrs : 20
SHOW SLOTTED PAGE HEADER¶
It shows the header information of specified slotted page.
SHOW SLOTTED PAGE HEADER { WHERE|OF } VOLUME = volume_num AND PAGE = page_num;
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Volume_id | INT | Volume id of the page |
| Page_id | INT | page id of the page |
| Num_slots | INT | Number of allocated slots for the page |
| Num_records | INT | Number of records on page |
| Anchor_type | VARCHAR(32) | One of flowing: ANCHORED, ANCHORED_DONT_REUSE_SLOTS, UNANCHORED_ANY_SEQUENCE, UNANCHORED_KEEP_SEQUENCE |
| Alignment | VARCHAR(8) | Alignment for records, one of flowing: CHAR, SHORT, INT, DOUBLE |
| Total_free_area | INT | Total free space on page |
| Contiguous_free_area | INT | Contiguous free space on page |
| Free_space_offset | INT | Byte offset from the beginning of the page to the first free byte area on the page |
| Need_update_best_hint | INT | True if saving is need for recovery (undo) |
| Is_saving | INT | True if we should update best pages hint for this page. |
| Flags | INT | Flag value of the page |
The following shows the examples of this syntax.
-- csql> ;line on
SHOW SLOTTED PAGE HEADER OF VOLUME=0 AND PAGE=140;
<00001> Volume_id : 0
Page_id : 140
Num_slots : 3
Num_records : 3
Anchor_type : 'ANCHORED_DONT_REUSE_SLOTS'
Alignment : 'INT'
Total_free_area : 15880
Contiguous_free_area : 15880
Free_space_offset : 460
Need_update_best_hint: 1
Is_saving : 0
Flags : 0
SHOW SLOTTED PAGE SLOTS¶
It shows the information of all slots in the specified slotted page.
SHOW SLOTTED PAGE SLOTS { WHERE|OF } VOLUME = volume_num AND PAGE = page_num;
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Volume_id | INT | Volume id of the page |
| Page_id | INT | Page id of the page |
| Slot_id | INT | The slot id |
| Offset | INT | Byte offset from the beginning of the page to the beginning of the record |
| Type | VARCHAR(32) | Record type, one of flowing: REC_UNKNOWN, REC_ASSIGN_ADDRESS, REC_HOME, REC_NEWHOME, REC_RELOCATION, REC_BIGONE, REC_MARKDELETED, REC_DELETED_WILL_REUSE |
| Length | INT | Length of record |
| Waste | INT | Whether or not wasted |
The following shows the examples of this syntax.
-- csql> ;line on
SHOW SLOTTED PAGE HEADER OF VOLUME=0 AND PAGE=140;
<00001> Volume_id: 0
Page_id : 140
Slot_id : 0
Offset : 40
Type : 'HOME'
Length : 292
Waste : 0
<00002> Volume_id: 0
Page_id : 140
Slot_id : 1
Offset : 332
Type : 'HOME'
Length : 64
Waste : 0
<00003> Volume_id: 0
Page_id : 140
Slot_id : 2
Offset : 396
Type : 'HOME'
Length : 64
Waste : 0
SHOW INDEX HEADER¶
It shows the index header page of the index of the table.
SHOW INDEX HEADER OF table_name.index_name;
If ALL keyword is used and an index name is omitted, it shows the entire headers of the indexes of the table.
SHOW ALL INDEXES HEADER OF table_name;
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Table_name | VARCHAR(256) | Table name |
| Index_name | VARCHAR(256) | Index name |
| Btid | VARCHAR(64) | BTID (volid|fileid|root_pageid) |
| Node_level | INT | Node level (1 for LEAF, 2 or more for NON_LEAF) |
| Max_key_len | INT | Maximum key length for the subtree |
| Num_oids | INT | Number of OIDs stored in the Btree |
| Num_nulls | INT | Number of NULLs (they aren't stored) |
| Num_keys | INT | Number of unique keys in the Btree |
| Topclass_oid | VARCHAR(64) | Topclass oid or NULL OID (non unique index)(volid|pageid|slotid) |
| Unique | INT | Unique or non-unique |
| Overflow_vfid | VARCHAR(32) | VFID (volid|fileid) |
| Key_type | VARCHAR(256) | Type name |
| Columns | VARCHAR(256) | the list of columns which consists of the index |
The following shows the examples of this syntax.
-- Prepare test environment
CREATE TABLE tbl1(a INT, b VARCHAR(5));
CREATE INDEX index_ab ON tbl1(a ASC, b DESC);
-- csql> ;line on
SHOW INDEX HEADER OF tbl1.index_ab;
<00001> Table_name : 'tbl1'
Index_name : 'index_a'
Btid : '(0|378|950)'
Node_type : 'LEAF'
Max_key_len : 0
Num_oids : -1
Num_nulls : -1
Num_keys : -1
Topclass_oid : '(0|469|4)'
Unique : 0
Overflow_vfid: '(-1|-1)'
Key_type : 'midxkey(integer,character varying(5))'
Columns : 'a,b DESC'
SHOW INDEX CAPACITY¶
It shows the index capacity of the index of the table.
SHOW INDEX CAPACITY OF table_name.index_name;
If ALL keyword is used and an index name is omitted, it shows the entire capacity of the indexes of the table.
SHOW ALL INDEXES CAPACITY OF table_name;
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Table_name | VARCHAR(256) | Table name |
| Index_name | VARCHAR(256) | Index name |
| Btid | VARCHAR(64) | BTID (volid|fileid|root_pageid) |
| Num_distinct_key | INT | Distinct key count (in leaf pages) |
| Total_value | INT | Total number of values stored in tree |
| Avg_num_value_per_key | INT | Average number of values (OIDs) per key |
| Num_leaf_page | INT | Leaf page count |
| Num_non_leaf_page | INT | NonLeaf page count |
| Num_total_page | INT | Total page count |
| Height | INT | Height of the tree |
| Avg_key_len | INT | Average key length |
| Avg_rec_len | INT | Average page record length |
| Total_space | VARCHAR(64) | Total space occupied by index |
| Total_used_space | VARCHAR(64) | Total used space in index |
| Total_free_space | VARCHAR(64) | Total free space in index |
| Avg_num_page_key | INT | Average page key count (in leaf pages) |
| Avg_page_free_space | VARCHAR(64) | Average page free space |
The following shows the examples of this syntax.
-- Prepare test environment
CREATE TABLE tbl1(a INT, b VARCHAR(5));
CREATE INDEX index_a ON tbl1(a ASC);
CREATE INDEX index_b ON tbl1(b ASC);
-- csql> ;line on
SHOW INDEX CAPACITY OF tbl1.index_a;
<00001> Table_name : 'tbl1'
Index_name : 'index_a'
Btid : '(0|378|950)'
Num_distinct_key : 0
Total_value : 0
Avg_num_value_per_key: 0
Num_leaf_page : 1
Num_non_leaf_page : 0
Num_total_page : 1
Height : 1
Avg_key_len : 0
Avg_rec_len : 0
Total_space : '16.0K'
Total_used_space : '116.0B'
Total_free_space : '15.9K'
Avg_num_page_key : 0
Avg_page_free_space : '15.9K'
SHOW ALL INDEXES CAPACITY OF tbl1;
<00001> Table_name : 'tbl1'
Index_name : 'index_a'
Btid : '(0|378|950)'
Num_distinct_key : 0
Total_value : 0
Avg_num_value_per_key: 0
Num_leaf_page : 1
Num_non_leaf_page : 0
Num_total_page : 1
Height : 1
Avg_key_len : 0
Avg_rec_len : 0
Total_space : '16.0K'
Total_used_space : '116.0B'
Total_free_space : '15.9K'
Avg_num_page_key : 0
Avg_page_free_space : '15.9K'
<00002> Table_name : 'tbl1'
Index_name : 'index_b'
Btid : '(0|381|960)'
Num_distinct_key : 0
Total_value : 0
Avg_num_value_per_key: 0
Num_leaf_page : 1
Num_non_leaf_page : 0
Num_total_page : 1
Height : 1
Avg_key_len : 0
Avg_rec_len : 0
Total_space : '16.0K'
Total_used_space : '120.0B'
Total_free_space : '15.9K'
Avg_num_page_key : 0
Avg_page_free_space : '15.9K'
SHOW CRITICAL SECTIONS¶
Total critical section (hereafter CS) information of a database is shown.
SHOW CRITICAL SECTIONS;
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Index | INT | The index of CS |
| Name | VARCHAR(32) | The name of CS |
| Num_holders | VARCHAR(16) | The number of CS holders. This has one of these values: 'N readers', '1 writer', 'none' |
| Num_waiting_readers | INT | The number of waiting readers |
| Num_waiting_writers | INT | The number of waiting writers |
| Owner_thread_index | INT | The thread index of CS owner writer, NULL if no owner |
| Owner_tran_index | INT | Transaction index of CS owner writer, NULL if no owner |
| Total_enter_count | BIGINT | Total count of enterers |
| Total_waiter_count | BIGINT | Total count of waiters |
| Waiting_promoter_thread_index | INT | The thread index of waiting promoter, NULL if no waiting promoter |
| Max_waiting_msecs | NUMERIC(10,3) | Maximum waiting time (millisecond) |
| Total_waiting_msecs | NUMERIC(10,3) | Total waiting time (millisecond) |
The following shows the examples of this syntax.
SHOW CRITICAL SECTIONS;
Index Name Num_holders Num_waiting_readers Num_waiting_writers Owner_thread_index Owner_tran_index Total_enter_count Total_waiter_count Waiting_promoter_thread_index Max_waiting_msecs Total_waiting_msecs
============================================================================================================================================================================================================================================================
0 'ER_LOG_FILE' 'none' 0 0 NULL NULL 217 0 NULL 0.000 0.000
1 'ER_MSG_CACHE' 'none' 0 0 NULL NULL 0 0 NULL 0.000 0.000
2 'WFG' 'none' 0 0 NULL NULL 0 0 NULL 0.000 0.000
3 'LOG' 'none' 0 0 NULL NULL 11 0 NULL 0.000 0.000
4 'LOCATOR_CLASSNAME_TABLE' 'none' 0 0 NULL NULL 33 0 NULL 0.000 0.000
5 'QPROC_QUERY_TABLE' 'none' 0 0 NULL NULL 3 0 NULL 0.000 0.000
6 'QPROC_LIST_CACHE' 'none' 0 0 NULL NULL 1 0 NULL 0.000 0.000
7 'DISK_CHECK' 'none' 0 0 NULL NULL 3 0 NULL 0.000 0.000
8 'CNV_FMT_LEXER' 'none' 0 0 NULL NULL 0 0 NULL 0.000 0.000
9 'HEAP_CHNGUESS' 'none' 0 0 NULL NULL 10 0 NULL 0.000 0.000
10 'TRAN_TABLE' 'none' 0 0 NULL NULL 7 0 NULL 0.000 0.000
11 'CT_OID_TABLE' 'none' 0 0 NULL NULL 0 0 NULL 0.000 0.000
12 'HA_SERVER_STATE' 'none' 0 0 NULL NULL 2 0 NULL 0.000 0.000
13 'COMPACTDB_ONE_INSTANCE' 'none' 0 0 NULL NULL 0 0 NULL 0.000 0.000
14 'ACL' 'none' 0 0 NULL NULL 0 0 NULL 0.000 0.000
15 'PARTITION_CACHE' 'none' 0 0 NULL NULL 1 0 NULL 0.000 0.000
16 'EVENT_LOG_FILE' 'none' 0 0 NULL NULL 0 0 NULL 0.000 0.000
17 'LOG_ARCHIVE' 'none' 0 0 NULL NULL 0 0 NULL 0.000 0.000
18 'ACCESS_STATUS' 'none' 0 0 NULL NULL 1 0 NULL 0.000 0.000
SHOW TRANSACTION TABLES¶
It shows internal information of transaction descriptors which is internal data structure to manage each transaction. It only shows valid transactions and the result may not be a consistent snapshot of a transaction descriptor.
SHOW { TRAN | TRANSACTION } TABLES [ WHERE EXPR ];
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Tran_index | INT | Index on the transaction table or NULL for unassigned transaction descriptor slot |
| Tran_id | INT | Transaction Identifier |
| Is_loose_end | INT | 0 for Ordinary transactions, 1 for loose-end transactions |
| State | VARCHAR(64) | State of the transaction. Either one of the followings: 'TRAN_RECOVERY', 'TRAN_ACTIVE', 'TRAN_UNACTIVE_COMMITTED', 'TRAN_UNACTIVE_WILL_COMMIT', 'TRAN_UNACTIVE_COMMITTED_WITH_POSTPONE', 'TRAN_UNACTIVE_ABORTED', 'TRAN_UNACTIVE_UNILATERALLY_ABORTED', 'TRAN_UNACTIVE_2PC_PREPARE', 'TRAN_UNACTIVE_2PC_COLLECTING_PARTICIPANT_VOTES', 'TRAN_UNACTIVE_2PC_ABORT_DECISION', 'TRAN_UNACTIVE_2PC_COMMIT_DECISION', 'TRAN_UNACTIVE_COMMITTED_INFORMING_PARTICIPANTS', 'TRAN_UNACTIVE_ABORTED_INFORMING_PARTICIPANTS','TRAN_STATE_UNKNOWN' |
| Isolation | VARCHAR(64) | Isolation level of the transaction. Either one of the followings: 'SERIALIZABLE', 'REPEATABLE READ', 'COMMITTED READ', 'TRAN_UNKNOWN_ISOLATION' |
| Wait_msecs | INT | Wait until this number of milliseconds for locks. |
| Head_lsa | VARCHAR(64) | First log address of transaction. |
| Tail_lsa | VARCHAR(64) | Last log record address of transaction. |
| Undo_next_lsa | VARCHAR(64) | Next log record address of transaction for UNDO purposes. |
| Postpone_next_lsa | VARCHAR(64) | Next address of a postpone record to be executed. |
| Savepoint_lsa | VARCHAR(64) | Address of last save-point. |
| Topop_lsa | VARCHAR(64) | Address of last top operation. |
| Tail_top_result_lsa | VARCHAR(64) | Address of last partial abort/commit. |
| Client_id | INT | Unique identifier of client application bind to transaction. |
| Client_type | VARCHAR(40) | Type of the client. Either one of the followings: 'SYSTEM_INTERNAL', 'DEFAULT', 'CSQL', 'READ_ONLY_CSQL', 'BROKER', 'READ_ONLY_BROKER', 'SLAVE_ONLY_BROKER', 'ADMIN_UTILITY', 'ADMIN_CSQL', 'LOG_COPIER', 'LOG_APPLIER', 'RW_BROKER_REPLICA_ONLY', 'RO_BROKER_REPLICA_ONLY', 'SO_BROKER_REPLICA_ONLY', 'ADMIN_CSQL_WOS', 'UNKNOWN' |
| Client_info | VARCHAR(256) | General information of client application. |
| Client_db_user | VARCHAR(40) | Current login database account from client application. |
| Client_program | VARCHAR(256) | Program name of client application. |
| Client_login_user | VARCHAR(16) | Current login user of OS which running the client application. |
| Client_host | VARCHAR(64) | Host name of client application. |
| Client_pid | INT | Process id of client application. |
| Topop_depth | INT | Depth of nested top operation. |
| Num_unique_btrees | INT | Number of unique btrees contained in unique_stat_info array. |
| Max_unique_btrees | INT | Size of unique_stat_info_array. |
| Interrupt | INT | The flag of whether or not interrupt current transaction. 0 for No, 1 for Yes. |
| Num_transient_classnames | INT | Number of transient classnames by this transaction. |
| Repl_max_records | INT | Capacity of replication record array. |
| Repl_records | VARCHAR(20) | Replication record buffer array, display address pointer as 0x12345678 or NULL for 0x00000000. |
| Repl_current_index | INT | Current position of replication record in the array. |
| Repl_append_index | INT | Current position of appended record in the array. |
| Repl_flush_marked_index | INT | Index of flush marked replication record at first. |
| Repl_insert_lsa | VARCHAR(64) | Insert Replication target LSA. |
| Repl_update_lsa | VARCHAR(64) | Update Replication target LSA. |
| First_save_entry | VARCHAR(20) | First save entry for the transaction, display address pointer as 0x12345678 or NULL for 0x00000000. |
| Tran_unique_stats | VARCHAR(20) | Local statistical info for multiple row. display address pointer as 0x12345678 or NULL for 0x00000000. |
| Modified_class_list | VARCHAR(20) | List of dirty classes, display address pointer as 0x12345678 or NULL for 0x00000000. |
| Num_temp_files | INT | Number of temporary files. |
| Waiting_for_res | VARCHAR(20) | Waiting resource. Just display address pointer as 0x12345678 or NULL for 0x00000000. |
| Has_deadlock_priority | INT | Whether or not have deadlock priority. 0 for No, 1 for Yes. |
| Suppress_replication | INT | Suppress writing replication logs when flag is set. |
| Query_timeout | DATETIME | A query should be executed before query_timeout time or NULL for waiting until query complete. |
| Query_start_time | DATETIME | Current query start time or NULL for query completed. |
| Tran_start_time | DATETIME | Current transaction start time or NULL for transaction completed. |
| Xasl_id | VARCHAR(64) | vpid:(volid|pageid),vfid:(volid|pageid) or NULL for query completed. |
| Disable_modifications | INT | Disable modification if greater than zero. |
| Abort_reason | VARCHAR(40) | Reason of transaction aborted. Either one of the followings: 'NORMAL', 'ABORT_DUE_TO_DEADLOCK', 'ABORT_DUE_ROLLBACK_ON_ESCALATION' |
The following shows the examples of the statement.
SHOW TRAN TABLES WHERE CLIENT_TYPE = 'CSQL';
=== <Result of SELECT Command in Line 1> ===
<00001> Tran_index : 1
Tran_id : 58
Is_loose_end : 0
State : 'ACTIVE'
Isolation : 'COMMITTED READ'
Wait_msecs : -1
Head_lsa : '(-1|-1)'
Tail_lsa : '(-1|-1)'
Undo_next_lsa : '(-1|-1)'
Postpone_next_lsa : '(-1|-1)'
Savepoint_lsa : '(-1|-1)'
Topop_lsa : '(-1|-1)'
Tail_top_result_lsa : '(-1|-1)'
Client_id : 108
Client_type : 'CSQL'
Client_info : ''
Client_db_user : 'PUBLIC'
Client_program : 'csql'
Client_login_user : 'cubrid'
Client_host : 'cubrid001'
Client_pid : 13190
Topop_depth : 0
Num_unique_btrees : 0
Max_unique_btrees : 0
Interrupt : 0
Num_transient_classnames: 0
Repl_max_records : 0
Repl_records : NULL
Repl_current_index : 0
Repl_append_index : -1
Repl_flush_marked_index : -1
Repl_insert_lsa : '(-1|-1)'
Repl_update_lsa : '(-1|-1)'
First_save_entry : NULL
Tran_unique_stats : NULL
Modified_class_list : NULL
Num_temp_files : 0
Waiting_for_res : NULL
Has_deadlock_priority : 0
Suppress_replication : 0
Query_timeout : NULL
Query_start_time : 03:10:11.425 PM 02/04/2016
Tran_start_time : 03:10:11.425 PM 02/04/2016
Xasl_id : 'vpid: (32766|50), vfid: (32766|43)'
Disable_modifications : 0
Abort_reason : 'NORMAL'
SHOW THREADS¶
It shows internal information of each thread. The results are sorted by "Index" column with ascending order and may not be a consistent snapshot of thread entries. The statement under SA MODE shows an empty result.
SHOW THREADS [ WHERE EXPR ];
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Index | INT | Thread entry index. |
| Jobq_index | INT | Job queue index only for worker threads. NULL for non-worker threads. |
| Thread_id | BIGINT | Thread id. |
| Tran_index | INT | Transaction index to which this thread belongs. If no related tran index, NULL. |
| Type | VARCHAR(8) | Thread type. Either one of the followings: 'MASTER', 'SERVER', 'WORKER', 'DAEMON', 'VACUUM_MASTER', 'VACUUM_WORKER', 'NONE', 'UNKNOWN'. |
| Status | VARCHAR(8) | Thread status. Either one of the followings: 'DEAD', 'FREE', 'RUN', 'WAIT', 'CHECK'. |
| Resume_status | VARCHAR(32) | Resume status. Either one of the followings: 'RESUME_NONE', 'RESUME_DUE_TO_INTERRUPT', 'RESUME_DUE_TO_SHUTDOWN', 'PGBUF_SUSPENDED', 'PGBUF_RESUMED', 'JOB_QUEUE_SUSPENDED', 'JOB_QUEUE_RESUMED', 'CSECT_READER_SUSPENDED', 'CSECT_READER_RESUMED', 'CSECT_WRITER_SUSPENDED', 'CSECT_WRITER_RESUMED', 'CSECT_PROMOTER_SUSPENDED', 'CSECT_PROMOTER_RESUMED', 'CSS_QUEUE_SUSPENDED', 'CSS_QUEUE_RESUMED', 'QMGR_ACTIVE_QRY_SUSPENDED', 'QMGR_ACTIVE_QRY_RESUMED', 'QMGR_MEMBUF_PAGE_SUSPENDED', 'QMGR_MEMBUF_PAGE_RESUMED', 'HEAP_CLSREPR_SUSPENDED', 'HEAP_CLSREPR_RESUMED', 'LOCK_SUSPENDED', 'LOCK_RESUMED', 'LOGWR_SUSPENDED', 'LOGWR_RESUMED' |
| Net_request | VARCHAR(64) | The net request name in net_requests array, such as: 'LC_ASSIGN_OID'. If not request name, shows NULL |
| Conn_client_id | INT | Client id whom this thread is responding, if no client id, shows NULL |
| Conn_request_id | INT | Request id which this thread is processing, if no request id, shows NULL |
| Conn_index | INT | Connection index, if not connection index, shows NULL |
| Last_error_code | INT | Last error code |
| Last_error_msg | VARCHAR(256) | Last error message, if message length is more than 256, it will be truncated, If no error message, shows NULL |
| Private_heap_id | VARCHAR(20) | The address of id of thread private memory allocator, such as: 0x12345678. If no related heap id, shows NULL. |
| Query_entry | VARCHAR(20) | The address of the QMGR_QUERY_ENTRY*, such as: 0x12345678, if no related QMGR_QUERY_ENTRY, shows NULL. |
| Interrupted | INT | 0 or 1, is this request/transaction interrupted |
| Shutdown | INT | 0 or 1, is server going down? |
| Check_interrupt | INT | 0 or 1 |
| Wait_for_latch_promote | INT | 0 or 1, whether this thread is waiting for latch promotion. |
| Lockwait_blocked_mode | VARCHAR(24) | Lockwait blocked mode. Either one of the followings: 'NULL_LOCK', 'IS_LOCK', 'S_LOCK', 'IS_LOCK', 'IX_LOCK', 'SIX_LOCK', 'X_LOCK', 'SCH_M_LOCK', 'UNKNOWN' |
| Lockwait_start_time | DATETIME | Start blocked time, if not in blocked state, shows NULL |
| Lockwait_msecs | INT | Time in milliseconds, if not in blocked state, shows NULL |
| Lockwait_state | VARCHAR(24) | The lock wait state such as: 'SUSPENDED', 'RESUMED', 'RESUMED_ABORTED_FIRST', 'RESUMED_ABORTED_OTHER', 'RESUMED_DEADLOCK_TIMEOUT', 'RESUMED_TIMEOUT', 'RESUMED_INTERRUPT'. If not in blocked state, shows NULL |
| Next_wait_thread_index | INT | The next wait thread index, if not exist, shows NULL |
| Next_tran_wait_thread_index | INT | The next wait thread index in lock manager, if not exist, shows NULL |
| Next_worker_thread_index | INT | The next worker thread index in css_Job_queue.worker_thrd_list, if not exist, shows NULL |
The following shows the examples of the statement.
SHOW THREADS WHERE RESUME_STATUS != 'RESUME_NONE' AND STATUS != 'FREE';
=== <Result of SELECT Command in Line 1> ===
<00001> Index : 183
Jobq_index : 3
Thread_id : 140077788813056
Tran_index : 3
Type : 'WORKER'
Status : 'RUN'
Resume_status : 'JOB_QUEUE_RESUMED'
Net_request : 'QM_QUERY_EXECUTE'
Conn_client_id : 108
Conn_request_id : 196635
Conn_index : 3
Last_error_code : 0
Last_error_msg : NULL
Private_heap_id : '0x02b3de80'
Query_entry : '0x7f6638004cb0'
Interrupted : 0
Shutdown : 0
Check_interrupt : 1
Wait_for_latch_promote : 0
Lockwait_blocked_mode : NULL
Lockwait_start_time : NULL
Lockwait_msecs : NULL
Lockwait_state : NULL
Next_wait_thread_index : NULL
Next_tran_wait_thread_index: NULL
Next_worker_thread_index : NULL
<00002> Index : 192
Jobq_index : 2
Thread_id : 140077779339008
Tran_index : 2
Type : 'WORKER'
Status : 'WAIT'
Resume_status : 'LOCK_SUSPENDED'
Net_request : 'LC_FIND_LOCKHINT_CLASSOIDS'
Conn_client_id : 107
Conn_request_id : 131097
Conn_index : 2
Last_error_code : 0
Last_error_msg : NULL
Private_heap_id : '0x02bcdf10'
Query_entry : NULL
Interrupted : 0
Shutdown : 0
Check_interrupt : 1
Wait_for_latch_promote : 0
Lockwait_blocked_mode : 'SCH_S_LOCK'
Lockwait_start_time : 10:47:45.000 AM 02/03/2016
Lockwait_msecs : -1
Lockwait_state : 'SUSPENDED'
Next_wait_thread_index : NULL
Next_tran_wait_thread_index: NULL
Next_worker_thread_index : NULL
SHOW JOB QUEUES¶
It shows the status of job queue. The statement under SA MODE shows an empty result.
SHOW JOB QUEUES;
This query has the following columns:
| Column name | Type | Description |
|---|---|---|
| Jobq_index | INT | The index of job queue |
| Num_total_workers | INT | Total number of work threads of the queue |
| Num_busy_workers | INT | The number of busy worker threads of the queue |
| Num_connection_workers | INT | The number of connection worker threads of the queue |